package org.clob4eclipse;

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import javax.sql.DataSource;

import oracle.jdbc.OracleConnection;
import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.OracleResultSet;
import oracle.sql.CLOB;

import org.apache.commons.dbcp.BasicDataSource;
import org.eclipse.jface.dialogs.MessageDialog;
import org.eclipse.swt.widgets.Shell;

public class Config {

	private Connection connection = null;
	private Statement statement = null;
	private PluginData data = null;
	
	public Config(PluginData data) throws Exception{
		try{
			this.data = data;
			this.setConnection(data);
		} catch (Exception e) {
			System.out.println("Failed to connect to DB " +
					"["+data.getConnectionString()+"] " + 
					"["+data.getUserName() + "/" + data.getPassword()+"]");
		}
	}
	
	public DataSource getDataSource(PluginData data) throws FileNotFoundException, IOException {
		BasicDataSource dataSource = new BasicDataSource();
		dataSource.setDriverClassName(PluginData.DRIVERCLASSNAME);
		dataSource.setUrl(data.getConnectionString());
		dataSource.setUsername(data.getUserName());
		dataSource.setPassword(data.getPassword());
		return dataSource;
	}

	public void setConnection(PluginData data) throws Exception {
		DataSource dataSource = this.getDataSource(data);
		connection = (Connection) dataSource.getConnection();
	}

    public int executeUpdate(String query){
		try {
			if (statement != null) {
				return statement.executeUpdate(query);
			} else throw new SQLException("Statement is null!");
		} catch (SQLException s) {
			System.out.println("Execute Update failed: "+query+" "+ s);
		}
		return 0;
    }

	public Connection getConnection() {
		return connection;
	}
	
	public OracleConnection getOracleConnection(PluginData data) throws Exception {
		      Connection con = null;
		      Class.forName(PluginData.DRIVERCLASSNAME);
		      
		      Properties props = new Properties();
		      props.put("user", data.getUserName() );
		      props.put("password", data.getPassword());
		      props.put("SetBigStringTryClob", "true");
		      
		      con=DriverManager.getConnection(
		        data.getConnectionString(),
		        props);
		      con.setAutoCommit(false);
		      PluginLogger.logInfo("driver name/version: "+con.getMetaData().getDriverName()+"/"+con.getMetaData().getDriverVersion());
		      PluginLogger.logInfo("db name/version: "+con.getMetaData().getDatabaseProductName()+"/"+con.getMetaData().getDatabaseProductVersion());
		      
		
		return (OracleConnection)con;
	}

	public Statement getStatement() throws SQLException {
		if (this.statement == null && this.connection != null) {
    		statement = this.getConnection().createStatement();
    	}		
		return statement;
	}
	
	
	public void commitClobFile(){
		
		
	}
	
	public void commitClobFile(InputStream stream, String fileName, Shell shell) throws Exception{
		
		OraclePreparedStatement ostmt = null;
		OracleConnection oc = null;
		
		try {
			oc = (OracleConnection) this.getOracleConnection(data);
			String query = "update "+data.getTableName()+" set "+data.getClobColumnName()+"=? where lower("+data.getFileNameColumn()+") = lower(?)";
			ostmt = (OraclePreparedStatement)oc.prepareStatement(query);
			
			String fileContent = inputStreamAsString(stream);
			
			PluginLogger.logInfo("query = "+query);
			
			ostmt.setStringForClob(1, fileContent);
			ostmt.setString(2, fileName);
			ostmt.executeQuery();
			
			MessageDialog.openInformation(
					shell,
					"OracleClobEditor Plug-in",
					"Save '"+fileName+"' executed sucessful!");
		} catch (SQLException e) {
			throw e;
		} finally {
			try {
				if (ostmt != null) ostmt.close();	
				if (oc != null)	oc.close();
			} catch (SQLException e) {
			}
			
		}
	}
	
	public static String inputStreamAsString(InputStream stream) throws IOException {
		BufferedReader br = new BufferedReader(new InputStreamReader(stream));
		StringBuilder sb = new StringBuilder();
		String line = null;

		while ((line = br.readLine()) != null) {
			sb.append(line + "\n");
		}

		br.close();
		return sb.toString();
	}
	
	
	
	
	public void commitClobFile2(InputStream stream, String fileName, Shell shell) throws SQLException, Exception {
		
		OracleConnection oc = null;
		OraclePreparedStatement	selostmt = null;
		ResultSet rset = null;
		String selQuery = null;
		CLOB clob = null;

		try	{
			
			String fileContent = inputStreamAsString(stream);
			
			oc = (OracleConnection) this.getOracleConnection(data);

			//switch off commits
			oc.setAutoCommit(false);

			selQuery = "select "+data.getClobColumnName()+" from "+data.getTableName()+" where lower("+data.getFileNameColumn()+") = lower(?) for update";
			
			PluginLogger.logInfo("query: "+selQuery);
			
			selostmt = (OraclePreparedStatement)oc.prepareStatement(selQuery);

			//set the parameters
			selostmt.setString(1, fileName);

			rset = selostmt.executeQuery();

			if (rset.next()) {
					//get the CLOB locators
				clob = ((OracleResultSet) rset).getCLOB(1);

				if ((clob != null) && (fileContent != null))
					writeTextToCLOB(clob, fileContent);
				
				PluginLogger.logInfo("one row selected length:"+clob.getLength()+" -- string length: "+fileContent.length());
				
			}

			//reinstate auto commit
			oc.setAutoCommit(true);

			//commit changes
			oc.commit();

			//close the resultset
			rset.close();
			rset = null;

			//close the statement
			selostmt.close();
			selostmt = null;
			
			MessageDialog.openInformation(
					shell,
					"OracleClobEditor Plug-in",
					"Save '"+fileName+"' executed sucessful!");
		}
		catch (SQLException ex) {
			PluginLogger.logError("Error:  ",ex);
			throw ex;
		}
		finally	{
			//reinstate auto commit
			if (!oc.getAutoCommit()) {
				oc.rollback();
				oc.setAutoCommit(true);
			}

				//close the resultset
			if (rset != null)
				rset.close();

				//close the statement
			if (selostmt != null)
				selostmt.close();
		}
	 }


	public static void writeTextToCLOB(CLOB c, String text)
	{
		if (c == null) return;

		Writer	w = null;

		try	{
				//get the writer from the clob (locator)
			w = c.getCharacterOutputStream();

				//write the page text
			w.write(text);

				//flush and close
			w.flush();
			w.close();
		}
		catch (Exception ex) {
			PluginLogger.logError("Error:  ",ex);
			try	{
				//try closing again
				w.close();
			} catch (Exception ex2) {;}
		}
	}

	
	
	
}
